﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbZConstruction : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbZConstruction()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0 )
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            gridControl2.DataSource = ds.Tables[0];
            gridView2.GroupPanelText = "共有" + ds.Tables[0].Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            { 
                dt.Rows.Add(dataRow.ItemArray);
            }
             
            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                string strHPPublicfile = row["环评公示文件"].ToString();
                string strZbgg = row["招标公告"].ToString();
                string strZhongBgg = row["中标公告"].ToString();
                string strBuildId = row["施工许可证号"].ToString();
                string strProName = row["工程名称"].ToString();
                string strBuildCompany = row["建设单位名称"].ToString();
                string strCompany = row["施工单位"].ToString();
                string strAddress = row["建设地点"].ToString();
                string strBuildingArea = row["工程规模（建筑面积）"].ToString();
                string strPrice = row["施工合同价格"].ToString();
                string strBeginDate = row["开工日期"].ToString();
                string strEndDate = row["计划竣工日期"].ToString();
                string strWrokbuilding = row["开工楼栋"].ToString();
                string strLicensingDate = row["发放施工许可证日期"].ToString();
                string strWorkBolume = row["开工量(㎡）"].ToString();
                string strWorkArea = row["施工面积（㎡）"].ToString();
                string strOverArea = row["竣工面积（㎡）"].ToString();
                string strLicenseId = row["规划许可证号"].ToString();               
                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tzconstruction (");
                    strSql.Append("HPPublicFile,");
                    strSql.Append("ZhaoBiao,");
                    strSql.Append("ZhongBiao,");
                    strSql.Append("BuildId,");
                    strSql.Append("ProjectName,");
                    strSql.Append("BuildCompany,");
                    strSql.Append("Company,");
                    strSql.Append("Address,");
                    strSql.Append("BuildingArea,");
                    strSql.Append("Price,");
                    strSql.Append("BeginDate,");
                    strSql.Append("EndDate,");
                    strSql.Append("WorkBuilding,");
                    strSql.Append("LicensingDate,");
                    strSql.Append("WorkVolume,");
                    strSql.Append("WorkArea,");
                    strSql.Append("OverArea,");
                    strSql.Append("LicenseId)");                     
                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strHPPublicfile);
                strSql.Append("','");
                strSql.Append(strZbgg);
                strSql.Append("','");
                strSql.Append(strZhongBgg);
                strSql.Append("','");
                strSql.Append(strBuildId);
                strSql.Append("','");
                strSql.Append(strProName);
                strSql.Append("','");
                strSql.Append(strBuildCompany);
                strSql.Append("','");
                strSql.Append(strCompany);
                strSql.Append("','");
                strSql.Append(strAddress);
                strSql.Append("','");
                strSql.Append(strBuildingArea);
                strSql.Append("','");
                strSql.Append(strPrice);
                strSql.Append("','");
                strSql.Append(strBeginDate);
                strSql.Append("','");
                strSql.Append(strEndDate);
                strSql.Append("','");
                strSql.Append(strWrokbuilding);
                strSql.Append("','");
                strSql.Append(strLicensingDate);
                strSql.Append("','");
                strSql.Append(strWorkBolume);
                strSql.Append("','");
                strSql.Append(strWorkArea);
                strSql.Append("','");
                strSql.Append(strOverArea);                
                strSql.Append("','");
                strSql.Append(strLicenseId);
                strSql.Append("'");  
                strSql.Append("),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
           
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }
  

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strProName = dr["工程名称"].ToString();
                string strBeginTime = dr["开工日期"].ToString();
                string strEndTime = dr["计划竣工日期"].ToString();
                string strLicensingDate = dr["发放施工许可证日期"].ToString();
                string strBeginArea = dr["施工面积（㎡）"].ToString();
                string strEndArea = dr["竣工面积（㎡）"].ToString();
                if (strProName.Trim() == "")
                {
                    strErrors.Append("第" + (i + 1) + "行数据中的【工程名称】为空！\r\n");
                }                 
                DateTime tempTime = new DateTime();
                if (!DateTime.TryParse(strBeginTime, out tempTime))
                {
                    strErrors.Append("第" + (i + 1)
                   + "行数据中【开工日期】的数据格式有误，应为时间格式【yyyy年MM月dd日】！\r\n");
                }
                if (!DateTime.TryParse(strEndTime, out tempTime))
                {
                    strErrors.Append("第" + (i + 1)
                   + "行数据中【计划竣工日期】的数据格式有误，应为时间格式【yyyy年MM月dd日】！\r\n");
                }
                if (!DateTime.TryParse(strLicensingDate, out tempTime))
                {
                    strErrors.Append("第" + (i + 1)
                   + "行数据中【发放施工许可证日期】的数据格式有误，应为时间格式【yyyy年MM月dd日】！\r\n");
                }
                float floatTemp = 0;
                if (!float.TryParse(strBeginArea,out floatTemp))
                {
                    strErrors.Append("第" + (i + 1)
                  + "行数据中【施工面积（㎡）】的数据格式有误，不是正确的数字！\r\n");
                }
                if (!float.TryParse(strEndArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 1)
                  + "行数据中【竣工面积（㎡）】的数据格式有误，不是正确的数字！\r\n");
                }
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n" +
                    strErrors);
                return false;
            }
            return true;
        }

        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("环评公示文件"))
            {
                strErrors.Append("【环评公示文件】、");
            }
            if (!dt.Columns.Contains("招标公告"))
            {
                strErrors.Append("【招标公告】、");
            }
            if (!dt.Columns.Contains("中标公告"))
            {
                strErrors.Append("【中标公告】、");
            }
            if (!dt.Columns.Contains("施工许可证号"))
            {
                strErrors.Append("【施工许可证号】、");
            }
            if (!dt.Columns.Contains("工程名称"))
            {
                strErrors.Append("【工程名称】、");
            }
            if (!dt.Columns.Contains("建设单位名称"))
            {
                strErrors.Append("【建设单位名称】、");
            }
            if (!dt.Columns.Contains("施工单位"))
            {
                strErrors.Append("【施工单位】、");
            }
            if (!dt.Columns.Contains("建设地点"))
            {
                strErrors.Append("【建设地点】、");
            }
            if (!dt.Columns.Contains("工程规模（建筑面积）"))
            {
                strErrors.Append("【工程规模（建筑面积）】、");
            }
            if (!dt.Columns.Contains("施工合同价格"))
            {
                strErrors.Append("【施工合同价格】、");
            }
            if (!dt.Columns.Contains("开工日期"))
            {
                strErrors.Append("【开工日期】");
            } 
            if (!dt.Columns.Contains("计划竣工日期"))
            {
                strErrors.Append("【计划竣工日期】");
            }
            if (!dt.Columns.Contains("开工楼栋"))
            {
                strErrors.Append("【开工楼栋】");
            }
            if (!dt.Columns.Contains("发放施工许可证日期"))
            {
                strErrors.Append("【发放施工许可证日期】");
            }
            if (!dt.Columns.Contains("开工量(㎡）"))
            {
                strErrors.Append("【开工量(㎡）】");
            }
            if (!dt.Columns.Contains("施工面积（㎡）"))
            {
                strErrors.Append("【施工面积（㎡）】");
            }
            if (!dt.Columns.Contains("竣工面积（㎡）"))
            {
                strErrors.Append("【竣工面积（㎡）】");
            }
            if (!dt.Columns.Contains("规划许可证号"))
            {
                strErrors.Append("【规划许可证号】");
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的广告投放Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
         
    }
}